﻿# region Using System
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Runtime.InteropServices;//捕捉错误
# endregion

# region Using ArcGIS
using ESRI.ArcGIS.Controls;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.esriSystem;//不用从引用加入
using ESRI.ArcGIS.Carto;//管理图层
using ESRI.ArcGIS.CartoUI;
using ESRI.ArcGIS.DataSourcesGDB;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.Display;
# endregion

namespace popGis
{
    public class DataConvertion
    {

        /// <summary>
        /// 将Datatable中的某列存为ArrayList集合
        /// </summary>
        /// <param name="thisTable">Datatable表名</param>
        /// <param name="fieldName">字段列名</param>
        /// <returns></returns>
        static public ArrayList getArrayListOfField(DataTable thisTable, string fieldName)
        {
            ArrayList thisList = new ArrayList();
            foreach (DataRow theRow in thisTable.Rows)
            {
                thisList.Add(theRow[fieldName]);
            }
            if (thisList == null) MessageBox.Show("返回空的集合。");

            return thisList;
        }

        /// <summary>
        /// 把 DataTable 转为 ITable
        /// </summary>
        /// <param name="pWorkspace">打开个人数据库对应的工作空间</param>
        /// <param name="dTable">从属性数据表中得到的数据表</param>
        /// <param name="tempTableName">临时文件名</param>
        /// <returns></returns>
        static public ITable DataTable2ITable(IWorkspace pWorkspace, DataTable dTable, string tempTableName)
        {
            IFeatureWorkspace pFeatureWorkspace = pWorkspace as IFeatureWorkspace;
            string path = pWorkspace.PathName;
            //获取表中记录数
            int rowCount = dTable.Rows.Count; 
            int fieldsCount = dTable.Columns.Count;

            DropTableInAccess(path, tempTableName);

            # region 构建字段集
            IFields fields = new FieldsClass();
            IFieldsEdit fieldsEdit = (IFieldsEdit)fields;
            fieldsEdit.FieldCount_2 = fieldsCount;

            IField pField = null;
            DataColumn pDataColumn;

            for (int i = 0; i < fieldsCount; i++)
            {
                pDataColumn = dTable.Columns[i];
                
                pField = new FieldClass();//新建一个并设置其属性 
                IFieldEdit fieldEdit = (IFieldEdit)pField;

                fieldEdit.Name_2 = dTable.Columns[i].Caption;
                fieldEdit.AliasName_2 = dTable.Columns[i].Caption;
                fieldEdit.Type_2 = ParseFieldType(pDataColumn.DataType.ToString());
                fieldEdit.Editable_2 = true;
                
                fieldsEdit.set_Field(i, pField);//字段添加到表中 

                pField = null;
                pDataColumn = null;
            }
            # endregion

            ITable pTable = pFeatureWorkspace.CreateTable(tempTableName, fieldsEdit, null, null, "");//创建空表

            # region 填充表
            try
            {
                //转换为ITable中的数据
                for (int k = 0; k < rowCount; k++)
                {
                    ICursor pCursor = pTable.Insert(true);
                    IRowBuffer row = pTable.CreateRowBuffer();//  不可以使用 IRow 接口，因为不一定含有 OID 字段
                    DataRow pRrow = dTable.Rows[k];
                    int rowNum = pRrow.ItemArray.Length;
                    
                    for (int n = 0; n < rowNum ; n++)
                    {                   
                        row.set_Value(n, pRrow.ItemArray.GetValue(n));//获得行向量
                    }
                    pCursor.InsertRow(row);//插入
                    pCursor.Flush();//保存
                }
                return pTable;
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message,"DataConvertion->ToITable()"); return null;
            }
            # endregion

        }

        /// <summary>
        /// 从Access数据库中删除指定名称的表
        /// </summary>
        /// <param name="mdbPath">Access数据库路径</param>
        /// <param name="dropTableName">要删除的表名称</param>
        /// <returns>表存在且成功删除，返回1；表不存在返回0,；其余返回-1</returns>
        static public int DropTableInAccess(string mdbPath, string dropTableName)
        {
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source="+mdbPath);
            conn.Open();
            DataTable dt = conn.GetSchema("Tables");
            if (dt != null)
            {
                int Cnt = dt.Rows.Count;
                for (int idx = Cnt -1 ; idx >=0 ; idx--)
                {
                    string tableType = dt.Rows[idx]["TABLE_TYPE"].ToString();
                    if (tableType == "TABLE")
                    {
                        string tableName = dt.Rows[idx]["TABLE_NAME"].ToString();

                        if (tableName == dropTableName)
                        {
                            string sql = "drop table [" + tableName + "]";
                            OleDbCommand command = new OleDbCommand(sql, conn);
                            int rowEffect = command.ExecuteNonQuery();
                            return 1;
                        }
                    }
                    if (idx < 0) return 0;
                }
            }
            conn.Close();
            return -1;
        }

        # region 测试用消息框

        /// <summary>
        /// 测试用消息框：查看Datatable的字段及类型
        /// </summary>
        /// <param name="thisTable"></param>
        static public void DisplayDataTableFields(DataTable thisTable)
        {
            string str = ""; string message = "";
            for (int i = 0; i < thisTable.Rows.Count; i++)
            {
                str = thisTable.Columns[i].Caption + "<" + thisTable.Columns[i].DataType.ToString().Substring(7) + ">\n";
                message += str + "  ";
            }
            MessageBox.Show(message, "[ " + thisTable.TableName + " ]  包含如下字段：");
        }

        /// <summary>
        /// 测试用消息框：查看ITable的字段及类型
        /// </summary>
        /// <param name="thisTable"></param>
        static public void DisplayITableFields(ITable thisTable)
        {
            string str = ""; string message = "";
            for (int i = 0; i < thisTable.Fields.FieldCount; i++)
            {
                str = thisTable.Fields.Field[i].Name + "\t<" + thisTable.Fields.Field[i].Type + ">\n";
                message += str;
            }
            MessageBox.Show(message, "此为ITable，包含如下字段：");
        }

        /// <summary>
        /// 测试用消息框：查看ITable的fieldname字段包含元素
        /// </summary>
        /// <param name="fieldname"></param>
        /// <param name="thisTable"></param>
        static public void DisplayFieldOfITable(string fieldname, ITable thisTable)
        {
            string mess = "";
            object o;
            int position = thisTable.FindField(fieldname);
            if (position < 0)
                mess = "No such field found!";
            else
            {
                ICursor pCursor = thisTable.Search(null, false);
                IRow pRow = pCursor.NextRow();
                while (pRow != null)
                {
                    o = pRow.get_Value(position);
                    mess += o.ToString() + "\n";
                    pRow = pCursor.NextRow();
                }
            }
            MessageBox.Show(mess, fieldname + "字段内容：");
        }

        # endregion

        # region 替换数据表字段名中的点，即Replace(".", "_")
        ///  <summary> 
        /// 替换数据表字段名中的点 
        ///  </summary> 
        ///  <param  name="FCname"></param> 
        ///  <returns></returns> 
        static public string getValidFeatureClassName(string FCname)
        {
            int dot = FCname.IndexOf(".");
            if (dot != -1)
            {
                return FCname.Replace(".", "_");
            }
            return FCname;
        }
        # endregion

        # region FieldType Convertion
        /// <summary>
        /// 将.Net相应的数据类型转换成GeoDatabase字段类型
        /// </summary>
        /// <param name="fieldType">地理数据字段类型</param>
        /// <returns>.Net数据类型</returns>
        /// 
        static public esriFieldType ParseFieldType(string fieldType)
        {
            switch (fieldType)
            {
                case "System.DateTime":
                    return esriFieldType.esriFieldTypeDate;
                case "System.Double":
                    return esriFieldType.esriFieldTypeDouble;
                case "System.String":
                    return esriFieldType.esriFieldTypeString;
                case "System.Int32":
                    return esriFieldType.esriFieldTypeInteger;
                case "System.Single":
                    return esriFieldType.esriFieldTypeSingle;
                default:
                    return esriFieldType.esriFieldTypeString;
            }
        }
        # endregion
    }

}
